<%--
  Created by IntelliJ IDEA.
  User: Albert Lee
  Date: 2020/5/27
  Time: 14:45
  To change this template use File | Settings | File Templates.
--%>

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">

    <title>学生信息</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
</head>

<body>
<%
    response.setCharacterEncoding("UTF-8");
    request.setCharacterEncoding("UTF-8");

    String id = request.getParameter("id");
    String name = request.getParameter("name");
    String age = request.getParameter("age");
    String gender = request.getParameter("gender");
    String major = request.getParameter("major");

    Connection conn = null;
    PreparedStatement stat = null;
    ResultSet rs = null;

    Class.forName("com.mysql.cj.jdbc.Driver");
    String url = "jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC";
    String user = "root";
    String password = "123456";
    conn = DriverManager.getConnection(url,user,password);

//    stat = conn.createStatement();
//    rs = stat.executeQuery("select * from stu");


%>
<br>
<h2>学生信息</h2>  <hr>
<br>
<h3>全部学生信息如下</h3>
<form action="delete_checkbox.jsp">
<table width="450" border="100" cellSpacing=3 style="font-size:15pt;border:dashed 1pt">
    <tr>
        <td> <%--若有学生数据则提供复选删除按钮--%>
            <%if(rs!=null){%>
                <input type="submit" value ="复选删除">
            <% } %>
        </td>
        <td>学号</td>
        <td>姓名</td>
        <td>年龄</td>
        <td>性别</td>
        <td>专业</td>
    </tr>
    <%
        //分页展示
        String pageN=request.getParameter("pageN");
        int pageSize=5;
        int lineCount;
        int pageCount;
        int pageNow=1;
        if(pageN!=null){
            pageNow=Integer.parseInt(pageN);
        }


        stat = conn.prepareStatement("select count(*) from stu ");
        rs = stat.executeQuery();
        rs.next();
        lineCount = rs.getInt(1);
        pageCount= lineCount%pageSize==0 ? lineCount/pageSize : lineCount/pageSize +1;
        if(pageNow<=0){
            pageNow=1;
        }
        if(pageNow>=pageCount){
            pageNow=pageCount;
        }

        stat = conn.prepareStatement("select * from stu limit ?,?");
        stat.setInt(1,pageSize*(pageNow-1));
       stat.setInt(2,pageSize);
        rs = stat.executeQuery();



        while(rs.next()) {
            Integer deleteid = rs.getInt("id");
            out.print("<tr>");
            out.println("<td>" + "<input type=\"checkbox\" name = \"delete_id\" value=\"" + deleteid + "\">" + "</td>");
            out.print("<td>" + rs.getInt("id") + "</td>");
            out.print("<td>" + rs.getString("name") + "</td>");
            out.print("<td>" + rs.getInt("age") + "</td>");
            out.print("<td>" + rs.getString("gender") + "</td>");
            out.print("<td>" + rs.getString("major") + "</td>");

    %>


<%-- 打印出来后面的修改个删除处--%>
    <td><a href="delete.jsp?id=<%=rs.getInt("id") %>">删除</a></td>
    <td><a href="update.jsp?id=<%=rs.getInt("id") %>">修改</a></td>
    <%
            out.print("</tr>");
        }
    %>
</table>

<%--分页页数操作--%>
    <%--罗列法--%>
    <div>
        <%for(int i = 1; i<=pageCount;i++){%>
            [<a href="showInfo.jsp?pageN=<%=i%>"> <%=i%> </a>]
        <% } %>
    </div>
    <br>

    <%--首上下尾搜索法--%>
    <div>
        [<a href="showInfo.jsp?pageN=1"> 首页 </a>]
        [<a href="showInfo.jsp?pageN=<%=pageNow-1%>"> 上一页 </a>]
        [<a href="showInfo.jsp?pageN=<%=pageNow+1%>"> 下一页 </a>]
        [<a href="showInfo.jsp?pageN=<%=pageCount%>"> 尾页 </a>]
        <br>
    </div>
    <br>

    <%--精准搜索--%>
    <div>
        <select onchange="location.replace('showInfo.jsp?pageN='+this.value);">
            <%for(int i = 1; i<=pageCount;i++){%>
                <%if(pageNow==i){%>
                    <option value="<%=i%>" selected="selected"> <%=i%> </option>
                <%}else{%>
                    <option value="<%=i%>"> <%=i%> </option>
                <%}%>
            <% } %>
        </select><br>
        总共<%=pageCount%>页，当前在<%=pageNow%>页
    </div>


</form>

<br>

<form action="select_for_id.jsp" method="post">
    <h3>按学号查询:<input type="text" name="id"  value="" title="学号不能为空"> </input>
        <input type="submit" value="查询"/></h3>
</form>

<form action="select_for_name.jsp" method="post">
    <h3>按姓名查询:<input type="text" name="name" value="" title="姓名不能为空"></input>
        <input type="submit" value="查询" /></h3>
</form>

<form action="select_for_age.jsp" method="post">
    <h3> 按年龄查询:<input type="text" name="age" value="" title="年龄不能为空"></input>
        <input type="submit" value="查询"/></h3>
</form>

<form action="select_for_gender.jsp" method="post">
    <h3> 按性别查询:<input type="text" name="gender" value="" title="性别不能为空"></input>
        <input type="submit" value="查询"/></h3>
</form>

<form action="select_for_major.jsp" method="post">
    <h3> 按专业查询:<input type="text" name="major" value="" title="专业不能为空"></input>
        <input type="submit" value="查询"/></h3>
</form>

<br>
<h3><a href=addStuInfo.jsp>返回添加学生信息页面</a></h3>

<br>

<%
    if(rs != null) {
        rs.close();
        rs = null;
    }
    if(stat != null) {
        stat.close();
        stat = null;
    }
    if(conn != null) {
        conn.close();
        conn = null;
    }
%>

</body>
</html>
